#!/usr/bin/env python
#-*-encoding:utf-8-*-
'''
Created on 2015年5月14日

@author: chenyongbing
'''
import sys,os,datetime
#current_dir = os.path.dirname(__file__)
current_dir = os.path.split(os.path.realpath(__file__))[0]

sys.path.append(os.path.join(current_dir,'../'))
from base.PyMySQL import ConnectMySQL
from base.localConfig import baseLocalConfig
host = baseLocalConfig.mysqlConfig['datatuning']['host']
user = baseLocalConfig.mysqlConfig['datatuning']['user']
passwd = baseLocalConfig.mysqlConfig['datatuning']['passwd']
db = baseLocalConfig.mysqlConfig['datatuning']['db']
myDataTuning = ConnectMySQL(host=host, user=user, password=passwd, db=db)


import logging


'''
    create table employee_rssi_stat (day datetime,device varchar(12) , rssi int , count int)
    create table employee_rssi_stats (day datetime,device varchar(12),mac varchar(17) , rssi int , count int)

'''


class DataTuningDatas():
    def __init__(self):
        pass
    
    def conv_tuple_to_str(self,alist=[]):
        if len(alist)==0:
            logging.error('List Is Null.')
            return ''
        
        if len(alist)==1:
            return '"%s"'%alist[0]
        blist = []
        for a in alist:
            blist.append(str(a))
        return '"'+'","'.join(blist)+'"'

    def insert_employee_rssi(self,tab='employee_rssi_stat',datas=[]):
        if datas==[]:return
        #sql = 'insert into '+tab+' (day,device,rssi,count) VALUES(%s,%s,%s,%s)'
        sql = 'insert into '+ tab + ' set day=%s,device=%s,rssi=%s,count=%s ON DUPLICATE KEY UPDATE count=%s'
        
        logging.debug("%s : %s"%(sql,len(datas)))
        myDataTuning.executemany(sql,datas)
    def new_insert_employee_rssi(self,tab='employee_rssi_stats',datas=[]):
        if datas==[]:return
        #sql = 'insert into '+tab+' (day,device,rssi,count) VALUES(%s,%s,%s,%s)'
        sql = 'insert into '+ tab + ' set day=%s,device=%s,mac=%s,rssi=%s,count=%s,type=%s ON DUPLICATE KEY UPDATE count=%s'
        
        logging.debug("%s : %s"%(sql,len(datas)))
        myDataTuning.executemany(sql,datas)  
    
    
    
    def search_employee_mac_rssi(self,tab='employee_rssi_stats',devices=[],startTime=None,endTime=None,useSysEmployee=0):
        query = 'select mac,rssi,sum(count) from %s '%tab
        if startTime !=None and endTime!=None:
            query += ' where day between "%s" and "%s"'%(startTime,endTime)
        else:
            query += ' where day="%s"'%datetime.date.today().strftime('%Y-%m-%d')
        if len(devices)!=0:
            query += ' and device in (%s)'%(self.conv_tuple_to_str(devices))
        if useSysEmployee==0:
            query += ' and type=1'
        elif useSysEmployee == 2:
            query += ' and type=0'
        
        query += ' group by rssi'
        logging.error(query)
        rets = myDataTuning.SelectAll(query)
        if rets==None:rets = []
#         if len(rets)==0 and useSysEmployee == 1:
#             return self.search_employee_mac_rssi(devices =  devices, startTime=startTime, endTime = endTime, useSysEmployee=2)
        datas = {}
        for ret in rets:
            mac = ret[0]
            rssi = ret[1]
            count = ret[2]
            if mac not in datas.keys():
                datas[mac] = {}
            datas[mac][rssi] = count
        return datas  
        
    def search_employee_rssi(self,tab='employee_rssi_stats',devices=[],startTime=None,endTime=None,useSysEmployee=0):
        query = 'select rssi,sum(count) from %s '%tab
        if startTime !=None and endTime!=None:
            query += ' where day between "%s" and "%s"'%(startTime,endTime)
        else:
            query += ' where day="%s"'%datetime.date.today().strftime('%Y-%m-%d')
        if len(devices)!=0:
            query += ' and device in (%s)'%(self.conv_tuple_to_str(devices))
        if useSysEmployee ==0:
            query += ' and type=1'
        elif useSysEmployee == 2:
            query += ' and type=0'
        query += ' group by rssi'
        logging.warn(query)
        rets = myDataTuning.SelectAll(query)
        if rets==None:rets = []
        datas = {}
        for ret in rets:
            datas[ret[0]] = ret[1]
        return datas
    
    
    
    def insert_flow_with_device_hour_rssi(self,tab='flow_with_device_hour_rssi',datas=[]):
        if len(datas)==0:return 
        sql = 'insert into '+ tab + ' set day=%s,device=%s,hour=%s,rssi=%s,count=%s ON DUPLICATE KEY UPDATE count=%s'
        logging.debug("%s : %s"%(sql,len(datas)))
        myDataTuning.executemany(sql,datas)  
        
myDataTuningDatas = DataTuningDatas()      